
package com.feib.ebilling.job.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;


/**
 * @title (#)BankingFeedbackDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/12/10 create by Jimmy Liu<br>
 */
@Repository("bankingFeedbackDao")
public class BankingFeedbackDao extends BaseDao {

    private SimpleJdbcInsert insertWriteOffPayment;
    private SimpleJdbcInsert insertCarloan_Cancel;
    private SimpleJdbcInsert insertPayment_Canceled;
    
    private SimpleJdbcInsert insertP0028_Master;
    private SimpleJdbcInsert insertP0028_Detail;
    
    /**
     * 虛擬帳號入帳實體帳號列表
     * @return 實體帳戶與業者名稱List
     */
    public List<Map<String, Object>> queryRealAccounts()
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select A.ACCOUNT_NO,B.GROUP_NAME from REAL_ACCOUNT A,APP_GROUP B ");
        sql.append("where A.GROUP_PK = B.GROUP_PK ORDER BY A.GROUP_PK");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }

    public int insertBankFeedbackLog(String[] contents, String fileName)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("insert into FEEDBACK_LOG (")
        .append("FBFILENAME, FBTIME, FBCODE, MRKEY, ADAY")
        .append(", ATIME, TBSDY, APTYPE, TXNO, DSCPT")
        .append(", TXAMT, USERNO, ATMID, KINBR, TRMSEQ")
        .append(", TXTNO, TFRBK, TFRACT, STANBK, STANSEQ")
        .append(", EC, TRMTYP, FEEDBACK_TYPE, CHANNEL")
        .append(") values ( ")
        .append("?, ?, ?, ?, ?")
        .append(", ?, ?, ?, ?, ?")
        .append(", ?, ?, ?, ?, ?")
        .append(", ?, ?, ?, ?, ?")
        .append(", ?, ?, ?, ?)");

        List<Object> args = new ArrayList<Object>();
        args.add(fileName);
        args.add(new Date());
        args.add("0");
        for (int i=0 ;i<=18; i++){
            if (i==7){
                /* 交易金額數字 */
                args.add(new Long(contents[7]));
            }else{
                /* 其他為文數字 */
                args.add(contents[i]); 
            }
        }
        args.add(new Integer(1)); //回饋種類 1 : 遠東銀行      2 : thirdParty(便利商店、郵局等)
        args.add(contents[18]);
        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }

    /**
     * @param fileName
     * @return
     */
    public int deleteBankFeedbackLog(String fileName) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from FEEDBACK_LOG where FBFILENAME = ? and FEEDBACK_TYPE = 1 ");
        return this.getJdbcTemplate().update(sql.toString(), fileName );
    }

    /**
     * 取得待繳款資料
     * @param virtualAccount
     * @return
     */
    public List<Map<String, Object>> queryUnWriteOffByVirtualAccount(String virtualAccount)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE")
        .append(" from PAYMENT where VIRTUAL_ACCOUNT ='")
        .append(virtualAccount).append("' AND PAYMENT_STATUS ='2'");
        System.out.println(sql);
        return this.getJdbcTemplate().queryForList(sql.toString());
    }

    /**
     * 取得已銷帳資料
     * @param virtualAccount
     * @param amt 金額
     * @return
     */
    public List<Map<String, Object>> queryWriteOffByVirtualAccount(String virtualAccount)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE, WRITE_OFF_PAYMENT_PK ")
        .append(" from PAYMENT where VIRTUAL_ACCOUNT ='")
        .append(virtualAccount).append("' AND PAYMENT_STATUS ='5'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }
    
    public List<Map<String, Object>> queryWriteOffByVirtualAccount2(String virtualAccount)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK,GROUP_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE, WRITE_OFF_PAYMENT_PK ")
        .append(" from PAYMENT where VIRTUAL_ACCOUNT like '")
        .append(virtualAccount).append("%' AND PAYMENT_STATUS ='2'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }

    /**
     * 新增一筆銷帳資料
     * @param writeoffPayment
     * @return 這一筆的PK
     */
    public Number insertWriteOffPayment(Map<String, Object> writeoffPayment)
    {
        if (null == insertWriteOffPayment)
        {
            insertWriteOffPayment =  new SimpleJdbcInsert(this.getDataSource()).withTableName("WRITE_OFF_PAYMENT")
            .usingColumns("PAYMENT_PK", "CHANNEL", "AMOUNT", "WRITE_OFF_DATE", "WRITE_OFF_STATUS"
                            , "FEEDBACK_UID", "CREATED_BY", "CREATED_DATE", "UPDATE_BY", "UPDATE_DATE"
                            , "TX_DATE") //繳款交易日  ADAY, add by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改
            .usingGeneratedKeyColumns("WRITE_OFF_PAYMENT_PK");
        }

        Map<String, Object> parameters = new HashMap<String, Object>(10);
        parameters.put("PAYMENT_PK", writeoffPayment.get("PAYMENT_PK"));
        parameters.put("CHANNEL", writeoffPayment.get("CHANNEL"));
        parameters.put("AMOUNT", writeoffPayment.get("AMOUNT"));
        parameters.put("TX_DATE", writeoffPayment.get("TX_DATE")); //繳款交易日  ADAY, add by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改
        parameters.put("WRITE_OFF_DATE", writeoffPayment.get("WRITE_OFF_DATE"));
        parameters.put("WRITE_OFF_STATUS", writeoffPayment.get("WRITE_OFF_STATUS"));

        parameters.put("FEEDBACK_UID", writeoffPayment.get("FEEDBACK_UID"));
        parameters.put("CREATED_BY", writeoffPayment.get("CREATED_BY"));
        parameters.put("CREATED_DATE", writeoffPayment.get("CREATED_DATE"));
        parameters.put("UPDATE_BY", writeoffPayment.get("UPDATE_BY"));
        parameters.put("UPDATE_DATE", writeoffPayment.get("UPDATE_DATE"));
        return insertWriteOffPayment.executeAndReturnKey(parameters);
    }

    public Map<String, Object> getWriteOffPayment(Number writeOffPaymentPk)
    {
        String sql = "SELECT * FROM WRITE_OFF_PAYMENT WHERE WRITE_OFF_PAYMENT_PK = " + writeOffPaymentPk;
        return DataAccessUtils.singleResult(this.getJdbcTemplate().queryForList(sql));
    }

    /**
     * 更新為已消帳
     * @param payment
     * @return
     */
    public int updatePaymentToWriteOffStatus(Map<String, Object> payment)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE PAYMENT SET ")
        .append("REAL_PAY_AMOUNT = ?")
        .append(", PAYMENT_STATUS = 5")
        .append(", WRITE_OFF_PAYMENT_PK = ? ")
        .append(", UPDATE_BY = ?")
        .append(", UPDATE_DATE = ? ")
        .append(" WHERE PAYMENT_PK = ? "); // 保險一點

        List<Object> args = new ArrayList<Object>();
        args.add(payment.get("REAL_PAY_AMOUNT"));
        args.add(payment.get("WRITE_OFF_PAYMENT_PK"));
        args.add(payment.get("UPDATE_BY"));
        args.add(payment.get("UPDATE_DATE"));
        args.add(payment.get("PAYMENT_PK"));

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }

    /**
     * 
     * 沖正<BR/>
     * 銷帳資料改為沖正銷帳 WRITE_OFF_PAYMENT.WRITE_OFF_STATUS = 5
     * @param number
     */
    public int rollbackWriteoff(Number writeOffPaymentPk) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE WRITE_OFF_PAYMENT SET ")
        .append("WRITE_OFF_STATUS = 5")
        .append(", UPDATE_BY = ? ")
        .append(", UPDATE_DATE = ? ")
        .append(" WHERE WRITE_OFF_PAYMENT_PK = ? ");

        List<Object> args = new ArrayList<Object>();
        args.add("BankingFeedBackJob");
        args.add(new Date());
        args.add(writeOffPaymentPk);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());

    }
    
    /**
     * 沖正
     * 繳費資料改為待繳款 PAYMENT.PAYMENT_STATUS = 2, PAYMENT.WRITE_OFF_PAYMENT_PK = null
     * @param number
     */
    public int rollbackPayment(Number PaymentPk) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE PAYMENT SET ")
        .append("REAL_PAY_AMOUNT = NULL")
        .append(", PAYMENT_STATUS = 2")
        .append(", WRITE_OFF_PAYMENT_PK = NULL ")
        .append(", UPDATE_BY = ? ")
        .append(", UPDATE_DATE = ? ")
        .append(" WHERE PAYMENT_PK = ? ");

        List<Object> args = new ArrayList<Object>();
        args.add("BankingFeedBackJob");
        args.add(new Date());
        args.add(PaymentPk);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());

    }
    
    public int rollbackPayment2(Number PaymentPk) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE PAYMENT SET ")
        .append("REAL_PAY_AMOUNT = NULL")
        .append(", PAYMENT_STATUS = 4")
        .append(", WRITE_OFF_PAYMENT_PK = NULL ")
        .append(", UPDATE_BY = ? ")
        .append(", UPDATE_DATE = ? ")
        .append(" WHERE PAYMENT_PK = ? ");

        List<Object> args = new ArrayList<Object>();
        args.add("BankingFeedBackJob");
        args.add(new Date());
        args.add(PaymentPk);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }

    public Number insertCarloan_Cancel(Map<String, Object> rd)
    {
        if (null == insertCarloan_Cancel)
        {
        	insertCarloan_Cancel =  new SimpleJdbcInsert(this.getDataSource()).withTableName("CARLOAN_CANCEL")
            .usingColumns("GROUP_PK" ,"FBFILENAME" ,"VIRTUAL_ACCOUNT" ,"CONTRACT_NO" 
            		,"CANCEL_TYPE" ,"CANCEL_DATE" ,"CANCEL_TERM") 
            .usingGeneratedKeyColumns("CARLOAN_CANCEL_PK");
        }
        return insertCarloan_Cancel.executeAndReturnKey(rd);
    }
    
    public Number insertPayment_Canceled(Map<String, Object> rd)
    {
        if (null == insertPayment_Canceled)
        {
        	insertPayment_Canceled =  new SimpleJdbcInsert(this.getDataSource()).withTableName("PAYMENT_CANCELED")
            .usingColumns("PAYMENT_PK" ,"CANCEL_DATE" ,"CANCEL_USER_PK" ,"CANCEL_REASON" 
            		,"CONFIRM_STATULS" ,"CONFIRM_DATE" ,"CONFIRM_USER_PK") 
            .usingGeneratedKeyColumns("PAYMENT_CANCELED_PK");
        }
        return insertPayment_Canceled.executeAndReturnKey(rd);
    }
    
    public List<Map<String, Object>> queryP0028(String itemcd, String mrkey, String userno)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT P0028_MPK from P0028_MASTER where ITEMCD ='")
        .append(itemcd).append("' AND MRKEY ='").append(mrkey).append("' AND USERNO ='").append(userno).append("'");        
        return this.getJdbcTemplate().queryForList(sql.toString());
    }
    
    public Number insertP0028_Master(Map<String, Object> rd)
    {
        if (null == insertP0028_Master)
        {
        	insertP0028_Master =  new SimpleJdbcInsert(this.getDataSource()).withTableName("P0028_MASTER")
            .usingColumns("ITEMCD", "MRKEY", "USERNO", "AVBAL", "UPDATE_DATE")            
            .usingGeneratedKeyColumns("P0028_MPK");
        }
        return insertP0028_Master.executeAndReturnKey(rd);
    }
    
    public int updateP0028_Master(Number P0028Pk, long bal) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE P0028_MASTER SET ")
        .append("AVBAL = ?,")
        .append("UPDATE_DATE = getdate() ")
        .append("WHERE P0028_MPK = ? ");
        
        List<Object> args = new ArrayList<Object>();
        args.add(bal);
        args.add(P0028Pk);
        
        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }
        
    public Number insertP0028_Detail(Map<String, Object> rd)
    {
        if (null == insertP0028_Detail)
        {
        	insertP0028_Detail =  new SimpleJdbcInsert(this.getDataSource()).withTableName("P0028_DETAIL")
            .usingColumns("ITEMCD", "MRKEY", "USERNO", "ADAY", "ATIME", "TBSDAY", "APTYPE", "TXNO",
            		      "DSCPT", "HCODE", "CRDB", "TXAMT", "AVBAL", "ATMID", "KINBR", "TRMSEQ",
            		      "TXTNO", "TFRBK", "FL", "TFRACT", "STANBK", "STANSEQ", "MEMO", "IMPORT_DATE") 
            .usingGeneratedKeyColumns("P0028_MPK");
        }
        return insertP0028_Detail.executeAndReturnKey(rd);
    }
    
    public int updateP0028_Date(String validDate) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE CODE_TABLE")
           .append("   SET CODE_VALUE = ?")
           .append(" WHERE CODE_KIND = 'P0028' ");
        
        List<Object> args = new ArrayList<Object>();
        args.add(validDate);
        
        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }
    /**
     * @param uid
     * @param string
     */
    public int updateWriteoffException(long uid, String errMsg) {
        
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE FEEDBACK_LOG SET ")
        .append("ERR_MSG = ?")
        .append(", FBCODE = 2 ") // 銷帳失敗/ 異常
        .append(" WHERE UID = ? ");

        List<Object> args = new ArrayList<Object>();
        args.add(errMsg);
        args.add(uid);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }

    /**
     * 是否已經有核銷檔案
     * @param fileName
     * @return
     */
    public boolean hasWriteoffLog(String fileName) {
        StringBuffer sql = new StringBuffer();
        sql.append("select count (*) from FEEDBACK_LOG")
        .append(" WHERE FBCODE = 1 ")
        .append("AND FBFILENAME = ? ");


        return  (0 < this.getJdbcTemplate().queryForInt(sql.toString(), fileName));
    }

    /**
     * @param fileName
     * @return
     */
    public int deleteLogsByFileName(String fileName) {
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM FEEDBACK_LOG ")
        .append(" WHERE FBFILENAME = ?  ");

        List<Object> args = new ArrayList<Object>();
        args.add(fileName);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
    }

    /**
     * @param uid
     */
    public int updateWriteoffDone(long uid) {
        
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE FEEDBACK_LOG SET ")
        .append(" FBCODE = 1 ") // 銷帳失敗/ 異常
        .append(" WHERE UID = ? ");

        List<Object> args = new ArrayList<Object>();
        args.add(uid);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());
        
    }

    /**
     * @param virtualAccount
     * @return
     */
    public int countRealTimeWriteOffByVirtualAccount(String virtualAccount) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(*) AS CNT FROM WRITE_OFF_PAYMENT WHERE RL_FLAG = 1 ")
        .append(" AND PAYMENT_PK IN (")
        .append(" SELECT PAYMENT_PK FROM PAYMENT WHERE VIRTUAL_ACCOUNT ='")
        .append(virtualAccount).append("' AND PAYMENT_STATUS ='5' ")
        .append(" )");

        return this.getJdbcTemplate().queryForInt(sql.toString());
    }
    
    public List<Map<String, Object>> queryFeedbacklogByGroupType(String virtualAccount) {
    	StringBuffer sql = new StringBuffer();
        sql.append("SELECT A.UID, A. FBFILENAME, A.FBTIME, A.ADAY, A.ATIME, A.ERR_MSG, A.CHANNEL AS ")
        .append("FEEDBACK_CHANNEL, A.TXAMT, A.USERNO , B.* FROM FEEDBACK_LOG A  JOIN ")
        .append("(SELECT G.GROUP_PK ,G.GROUP_NO, G.GROUP_NAME, R.VIRTUAL_ACCOUNT_NO ")
        .append(",LEN(R.VIRTUAL_ACCOUNT_NO) AS length FROM REAL_ACCOUNT R  JOIN APP_GROUP G ON ")
        .append("R.GROUP_PK = G.GROUP_PK WHERE G.GROUP_TYPE ='4') B  ON ")
        .append("left(RTRIM(A.USERNO),B.length) = B.VIRTUAL_ACCOUNT_NO where A.USERNO ='")
        .append(virtualAccount).append("' ");
        return this.getJdbcTemplate().queryForList(sql.toString());
    } 

}
